package cn.haohaowo.spring.chapter7;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlInOutParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.rowset.SqlRowSet;


public class JdbcTemplateTest {

	private static JdbcTemplate jdbcTemplate;
	
	@BeforeClass
	public static void setUpClass() {
		String url = "jdbc:mysql://localhost:3306/haohaowo?useUnicode=true&characterEncoding=utf-8";
		String username = "root";
		String password = "123456";
		String driver = "com.mysql.jdbc.Driver";
		
//		String url = "jdbc:hsqldb:mem:test";
//		String username = "sa";
//		String password = "";
//		String driver = "org.hsqldb.jdbcDriver";
		
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url, username, password);
		dataSource.setDriverClassName(driver);
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
//	@Test
	public void test() {
		//register sql
		String sql = " select * from INFORMATION_SCHEMA.SYSTEM_TABLES ";
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				//result
				String value = rs.getString("TABLE_NAME");
				System.out.println("column TABLE_NAME:" + value);
			}
		});
	}
	
	@Before
	public void setUp() {
		String createTableSql = "create table if not exists test " +
				"(id int(10) primary key auto_increment, " +
				"name varchar(20))";
//		String createTableSql = " create memory table test (id int generated by " +
//				" default as identity primary key, name varchar(100)) ";
		jdbcTemplate.update(createTableSql);
		
//		String createHsqldbFunctionSql = " create function function_test(str char(100)) " +
//				" returns int begin return length(str);end ";
//		jdbcTemplate.update(createHsqldbFunctionSql);
		
		String createHsqldbProcedureSql = " create procedure procedure_test" +
				"(inout inoutname varchar(100), out outid int) " +
				" begin " +
				"	insert into test(name) values(inoutname); " +
				"	select id into outid from test; " +
				"	set inoutname = concat('hello,', inoutname); " +
				"end";
		jdbcTemplate.execute(createHsqldbProcedureSql);
	}
	
	@After
	public void tearDown() {
//		jdbcTemplate.execute("drop function function_test");
		jdbcTemplate.execute("drop procedure procedure_test");
		
		String dropTableSql = "drop table test";
		jdbcTemplate.execute(dropTableSql);
	}
	
//	@Test
	public void testCURD() {
		insert();
		delete();
		update();
		select();
	}
	
	private void select() {
		jdbcTemplate.query("select * from test", new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				System.out.println("id:" + rs.getInt("id"));
				System.out.println("name:" + rs.getString("name"));
			}
		});
	}

	private void update() {
		jdbcTemplate.update("update test set name = 'name3' where name = ?", new Object[]{"name1"});
	}

	private void delete() {
		jdbcTemplate.update("delete from test where name = ?", new Object[]{"name2"});
		Assert.assertEquals(1,jdbcTemplate.queryForInt("select count(*) from test"));
	}

	private void insert() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		Assert.assertEquals(2,jdbcTemplate.queryForInt("select count(*) from test"));
	}
	
//	@Test
	public void testPreparedStatement1() {
		int count = jdbcTemplate.execute(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				return con.prepareStatement("select count(*) from test");
			}
		}, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement pstmt)
					throws SQLException, DataAccessException {
				pstmt.execute();
				ResultSet rs = pstmt.getResultSet();
				rs.next();
				return rs.getInt(1);
			}
		});
		Assert.assertEquals(1, count);
	}
	
//	@Test
	public void testPreparedStatement2() {
		String insertSql = "insert into test(name) values(?)";
		int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt) throws SQLException {
				pstmt.setObject(1, "name4");
			}
		});
		
		Assert.assertEquals(1, count);
		String deleteSql = "delete from test where name = ?";
		count = jdbcTemplate.update(deleteSql, new Object[]{"name4"});
		Assert.assertEquals(1, count);
	}
	
//	@Test
	public void testResultSet1() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		List result = jdbcTemplate.query(listSql, new RowMapper<Map>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rs.getInt("id"), rs.getString("name"));
				return row;
			}
		});
		
		Assert.assertEquals(1, result.size());
		jdbcTemplate.update("delete from test where name = 'name5'");
	}
	
//	@Test
	public void testResultSet2() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		final List result = new ArrayList();
		jdbcTemplate.query(listSql, new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Map row = new HashMap();
				row.put(rs.getInt("id"), rs.getString("name"));
				result.add(row);
			}
		});
		
		Assert.assertEquals(1, result.size());
		jdbcTemplate.update("delete from test where name = 'name5'");
	}
	
//	@Test
	public void testResultSet3() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		String listSql = "select * from test";
		List result = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() {
			@Override
			public List extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List result = new ArrayList();
				while(rs.next()) {
					Map row = new HashMap();
					row.put(rs.getInt("id"), rs.getString("name"));
					result.add(row);
				}
				return result;
			}
		});
		
		Assert.assertEquals(0, result.size());
		jdbcTemplate.update("delete from test where name = 'name5'");
	}
	
//	@Test
	public void testSelectMethod() {
		jdbcTemplate.update("insert into test(name) values('name5')");
		
		int count = jdbcTemplate.queryForInt("select count(*) from test");
		Assert.assertEquals(1, count);
		
		Map map = jdbcTemplate.queryForMap("select * from test where name = 'name5'");
		Assert.assertEquals(2, map.size());
		
		count = jdbcTemplate.queryForObject("select count(*) from test", Integer.class);
		Assert.assertEquals(1, count);
		
		List list = jdbcTemplate.queryForList("select * from test");
		Assert.assertEquals(1, list.size());
		
		list = jdbcTemplate.queryForList("select name from test where name = ?",
				new Object[]{"name5"}, String.class);
		Assert.assertEquals(1, list.size());
		
		SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from test");
		Assert.assertEquals(1, rs.getRow());
	}
	
//	@Test
	public void testCallableStatementCreator1() {
		final String callFunctionSql = "{?= call function_test(?)}";
		List<SqlParameter> params = new ArrayList<SqlParameter>();
		params.add(new SqlOutParameter("result", Types.INTEGER));
		params.add(new SqlParameter("str", Types.VARCHAR));
		
		
		Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
					CallableStatement cstmt = con.prepareCall(callFunctionSql);
					cstmt.registerOutParameter(1, Types.INTEGER);
					cstmt.setString(2, "test");
				return cstmt;
			}
		}, params);
		
		Assert.assertEquals(4, outValues.get("result"));
	}
	
//	@Test
	public void testNamedParameterJdbcTemplate1() {
		NamedParameterJdbcTemplate namedParameterJdbcTemplate =
				new NamedParameterJdbcTemplate(jdbcTemplate);
		String insertSql = " insert into test(name) values(:name) ";
		String selectSql = " select * from test where name = :name ";
		String deleteSql = " delete from test where name = :name ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("name", "name5");
		
		namedParameterJdbcTemplate.update(insertSql, paramMap);
		
		final List<Integer> result = new ArrayList<Integer>();
		namedParameterJdbcTemplate.query(selectSql, paramMap, new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				result.add(rs.getInt("id"));
			}
		});
		Assert.assertEquals(1, result.size());
		
		SqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
		namedParameterJdbcTemplate.update(deleteSql, paramSource);
	}
	
//	@Test
	public void testNamedParameterJdbcTemplate2() {
		NamedParameterJdbcTemplate namedParameterJdbcTemplate =
				new NamedParameterJdbcTemplate(jdbcTemplate);
		UserModel userModel = new UserModel();
		userModel.setMyName("name5");
		String insertSql = " insert into test(name) values(:myName) ";
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(userModel);
		namedParameterJdbcTemplate.update(insertSql, paramSource);
	}
	
//	@Test
	public void testSimpleJdbcTemplate() {
		SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(jdbcTemplate);
		String insertSql = " insert into test(name) values(?) ";
		simpleJdbcTemplate.update(insertSql, "name5");
		String selectSql = " select * from test where name = ? ";
		List<Map<String, Object>> result = simpleJdbcTemplate.queryForList(selectSql, "name5");
		Assert.assertEquals(1, result.size());
		RowMapper<UserModel> mapper = new UserRowMapper();
		List<UserModel> result2 = simpleJdbcTemplate.query(selectSql, mapper, "name5");
		Assert.assertEquals(1, result2.size());
	}
	
	@Test
	public void testCallableStatementCreator3() {
		final String callProcedureSql = "{call procedure_test(?, ?)}";
		List<SqlParameter> params = new ArrayList<SqlParameter>();
		params.add(new SqlInOutParameter("inoutname", Types.VARCHAR));
		params.add(new SqlOutParameter("outid", Types.INTEGER));
		Map<String, Object> outvalues = jdbcTemplate.call(new CallableStatementCreator() {
			
			@Override
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cstmt = con.prepareCall(callProcedureSql);
				cstmt.registerOutParameter(1, Types.VARCHAR);
				cstmt.registerOutParameter(2, Types.INTEGER);
				cstmt.setString(1, "test");
				return cstmt;
			}
		}, params);
		
		Assert.assertEquals("hello,test", outvalues.get("inoutname"));
		Assert.assertEquals(1, outvalues.get("outid"));
	}
}
